package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.*;

public class DBInvoice implements IFDBInvoice {
	
    private  Connection con;
    /** Creates a new instance of DBEmployee */
    public DBInvoice() {
      con = DbConnection1.getInstance().getDBcon();
    }

	public ArrayList<Invoice> getAllInvoices(boolean retrieveAssociation) {
		// Auto-generated method stub
		return miscWhere("", retrieveAssociation);
	}

	public Invoice findInvoice(int invoiceNo) {
		//Auto-generated method stub
		String wClause = "  invoiceNo = '" + invoiceNo + "'";
        return singleWhere(wClause);
	}
	
	public int findMaxInvoiceNo()
	
	{	
	int maxNO = GetMax.getMaxId("Select max(invoiceno) from invoice");
	return maxNO;
	}

	@Override
	public int insertInvoice(Invoice inv) throws Exception {
		// Auto-generated method stub
		//call to get the next ID number
        int nextID = GetMax.getMaxId("Select max(invoiceNo) from invoice");
        nextID = nextID + 1;
        System.out.println("next invoiceNO = " +  nextID);
  
       int rc = -1;
	   String query="INSERT INTO invoice(invoiceno, paymentdate, amount)  VALUES('"+
	  		nextID + "','" +
			inv.getPaymentDate()  + "','"  +
            inv.getAmount() + "')";
                     

       System.out.println("insert : " + query);
      try{ // insert new invoice
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Invoice ikke oprettet");
          throw new Exception ("invoice is not inserted correct");
       }
       return(rc);
	}

	
	private Invoice singleWhere(String wClause)
    {	
        ResultSet results;
        Invoice invObj = new Invoice();
        String query =  buildQuery(wClause);
        System.out.println("DbInvoice -singleWhere " + query); 
        try{ // read from project
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       invObj = buildInvoice(results);
               //missing the test on retriveassociation
               
           }//end if
	   stmt.close();
	}//slut try	
	catch(Exception e){
	   System.out.println("Query exception - select invoice : "+e);
	   e.printStackTrace();
        }
	return invObj;

    
    }
	
	 //miscWhere is used when more than one laaner is selected and build
    private ArrayList<Invoice> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Invoice> list = new ArrayList<Invoice>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbInvoice " + query); 
  		try{ // read from project
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
		     	 Invoice invObj = new Invoice();
			 invObj = buildInvoice(results);	
                         list.add(invObj);
                         //missing tes on retriveAssociation
			}//end while
			stmt.close();
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select invoice : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private Invoice buildInvoice(ResultSet results)
    {
       Invoice invObj = new Invoice();
       
         try{
               invObj.setInvoiceNumber(results.getInt("invoiceno"));
               invObj.setPaymentDate(results.getString("paymentdate"));
               invObj.setAmount(results.getInt("amount"));
               
          }
         catch(Exception e)
         {
             System.out.println("building Invoice object");
         }
        
        return invObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT invoiceNo, paymentdate, amount FROM invoice"; 
            
           

		if (wClause.length()>0)
			query = query + "  where " + wClause;
			
		return query;
	}

	


}
